package com.example.testdiyexceltableheader.demos.excel;


import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.example.testdiyexceltableheader.demos.model.Target;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.List;
import java.util.Map;

public class HeaderWriteHandler implements SheetWriteHandler {

    private final List<Target> headerList;
    Map<String, List<String>> roles;
    int lastRow = 1000;

    public HeaderWriteHandler(List<Target> headerList, Map<String, List<String>> roles) {
        this.headerList = headerList;
        this.roles = roles;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        if (!headerList.isEmpty()) {
            Sheet sheet = writeSheetHolder.getSheet();
            Row row = sheet.createRow(0);
            for (int i = 0; i < headerList.size(); i++) {

                Cell cell = row.createCell(i);
                Target target = headerList.get(i);
                cell.setCellValue(target.getName());

                if (target.getType().equals("dict")) {
                    dropDown(i, writeSheetHolder, target, writeWorkbookHolder);
                }
            }
            sheet.createFreezePane(1, 1, 1, 1);

        }
    }


    private void dropDown(int columnIndex, WriteSheetHolder writeSheetHolder, Target target, WriteWorkbookHolder writeWorkbookHolder) {


        Workbook workbook = writeWorkbookHolder.getWorkbook();
        String hiddenName = "hidden-valid-" + target.getDictKey();

        Sheet validSheet = workbook.getSheet(hiddenName);
        //循环规则
        List<String> cellRole = roles.get(target.getDictKey());
        System.out.println(target.getDictKey() + cellRole);
        if (validSheet == null) {

            validSheet = workbook.createSheet(hiddenName);

            for (int i = 0; i < cellRole.size(); i++) {
                Row row = validSheet.createRow(i);
                Cell cell = row.createCell(0);
                cell.setCellValue(cellRole.get(i));
            }
            //将新建的sheet页隐藏掉
            int sheetIndex = workbook.getSheetIndex(validSheet);
//            workbook.setSheetHidden(sheetIndex, true);
        }

        //获取新sheet页内容  ='name'!$A$0:$A$N
        String refers = String.format("='%s'!$A$1:$A$%s", hiddenName, cellRole.size());
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();

        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList cellAddresses = new CellRangeAddressList(1, lastRow, columnIndex, columnIndex);
        DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
        DataValidation validation = helper.createValidation(constraint, cellAddresses);
        sheet.addValidationData(validation);

    }

}
